import type { Cell } from '../types/sheet';

export class FormulaParser {
  private sheet: { [key: string]: Cell };

  constructor(sheet: { [key: string]: Cell }) {
    this.sheet = sheet;
  }

  evaluate(formula: string, currentCell: string): string | number {
    if (!formula.startsWith('=')) {
      return formula;
    }

    try {
      const expression = formula.substring(1).toUpperCase();
      return this.evaluateExpression(expression, currentCell);
    } catch (error) {
      return '#ERROR!';
    }
  }

  private evaluateExpression(expression: string, currentCell: string): string | number {
    // 处理基本函数
    if (expression.startsWith('SUM(')) {
      return this.calculateSum(expression);
    }
    if (expression.startsWith('AVERAGE(')) {
      return this.calculateAverage(expression);
    }

    // 处理单元格引用和基本运算
    return this.evaluateBasicExpression(expression);
  }

  private getCellValue(cellRef: string): number {
    const cell = this.sheet[cellRef];
    if (!cell) return 0;
    const value = cell.computedValue || cell.value;
    return Number(value) || 0;
  }

  private calculateSum(expression: string): number {
    const range = this.parseRange(expression.slice(4, -1));
    return range.reduce((sum, cell) => sum + this.getCellValue(cell), 0);
  }

  private calculateAverage(expression: string): number {
    const range = this.parseRange(expression.slice(8, -1));
    const sum = range.reduce((sum, cell) => sum + this.getCellValue(cell), 0);
    return sum / range.length;
  }

  private parseRange(range: string): string[] {
    const [start, end] = range.split(':');
    if (!end) return [start];

    const startCol = start.match(/[A-Z]+/)[0];
    const startRow = parseInt(start.match(/\d+/)[0]);
    const endCol = end.match(/[A-Z]+/)[0];
    const endRow = parseInt(end.match(/\d+/)[0]);

    const cells: string[] = [];
    for (let col = this.colToNum(startCol); col <= this.colToNum(endCol); col++) {
      for (let row = startRow; row <= endRow; row++) {
        cells.push(`${this.numToCol(col)}${row}`);
      }
    }
    return cells;
  }

  private colToNum(col: string): number {
    return col.split('').reduce((acc, char) => acc * 26 + char.charCodeAt(0) - 64, 0);
  }

  private numToCol(num: number): string {
    let result = '';
    while (num > 0) {
      num--;
      result = String.fromCharCode(65 + (num % 26)) + result;
      num = Math.floor(num / 26);
    }
    return result;
  }

  private evaluateBasicExpression(expression: string): number {
    // 替换单元格引用为实际值
    const valueExpression = expression.replace(/[A-Z]+\d+/g, (match) => {
      return this.getCellValue(match).toString();
    });
    
    // 安全地计算表达式
    return Function(`'use strict'; return (${valueExpression})`)();
  }
} 